﻿if object_id('fn_MapHTML_AssetDistributionPole2') is not null
	DROP FUNCTION fn_MapHTML_AssetDistributionPole2

GO

CREATE FUNCTION [dbo].[fn_MapHTML_AssetDistributionPole2]
(
	@PoleId int
)
RETURNS varchar(max)
AS
BEGIN

declare @html varchar(max)
declare @urlInspectionVisit varchar(max)

select 
	@urlInspectionVisit = SettingValue
from 
	tb_Settings 
where 
	SettingId = 23

SELECT @urlInspectionVisit = @urlInspectionVisit + 'InspVisitDistributionPole.aspx'

set @html = 
(
	select
		 'Distribution Pole' as 'TITLE'
		 ,'Inspection History' as 'HistoryTitle'
		,Poles.Utility_Asset_DistributionId
		,Poles.D804
		,Poles.[Year]
		,STR(Poles.Latitude, 9, 9) as 'Latitude'
		,STR(Poles.Longitude, 9, 9)  as 'Longitude'
		,Poles.Number as 'Number'
		,maps.Name as 'MapName'
		,sections.Name as 'SectionName'
		,species.Name as 'SpecieName'
		,classes.Name as 'ClassName'
		,lenghts.Name as 'LenghtName'
				
		-- Asset phases 
		,(select
			phases.Name 'PhaseName'
			from
				tb_Utility_Asset_Distribution_PhasesXPole assetPhases
				join tb_Distribution_Phases phases
					on phases.PhaseId = assetPhases.PhaseFk
			where 
				assetPhases.Utility_Asset_DistributionFK = Poles.Utility_Asset_DistributionId 
			for xml path ('PHASE'), type) as 'PHASES'

		--Asset tops 
		,(select
				tops.Name 'TopName'
			from
				tb_Utility_Asset_Distribution_TopsXPole assetTops
				join tb_Distributions_TopTypes tops
					on tops.Distributions_TopTypeId = assetTops.TopTypeFk
			where 
				assetTops.Utility_Asset_DistributionFK = Poles.Utility_Asset_DistributionId 
			for xml path ('TOP'), type) as 'TOPS'

		,(select 
				 'Inspection Visit' as 'TITLE'
				,vendors.Name as 'VendorName'
				,year(SubVisit.AcquisitionDate) AS 'YearVisited'
				,SubVisit.Vendor_Distribution_Poles_PoleId AS 'ID' 
				,SubVisit.InspBy AS 'LastVisitorName'
				,YEAR(SubVisit.LVisit) AS 'LastVisitorDate'
				,@urlInspectionVisit + '?ID=' + CONVERT(varchar, SubVisit.Vendor_Distribution_Poles_PoleId) AS urlInspectionVisit
				-- visit images <IMAGE href="" src="" />
				,(select
					 replace(replace(replace(replace(Src.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'InspectionPoleDistribution'), '{2}', 85), '{3}', 85) '@src'
					--,replace(replace(replace(replace(Href.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'InspectionPoleDistribution'), '{2}', 640), '{3}', 480) as '@href'
					,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'InspectionPoleDistribution'), '{2}', 640), '{3}', 480)) as '@href'
					,Images.*
				from 
					tb_Vendor_Distribution_Images as Images
				where 
					Images.Vendor_Distribution_PoleFk = SubVisit.Vendor_Distribution_Poles_PoleId
				for xml path ('IMAGE'), type) as 'IMAGES'
			from 
				tb_Vendor_Distribution_Poles as SubVisit
			inner join tb_vendors vendors
				on vendors.VendorId = SubVisit.VendorFk
			where
				SubVisit.Utility_Asset_Distributions_PoleFk = Poles.Utility_Asset_DistributionId
				and SubVisit.DistributionType = 1
			order by
				SubVisit.AcquisitionDate DESC
			for xml path ('VISIT'), type) as 'VISITS'

	from
		tb_Utility_Asset_Distributions as Poles
		left join tb_Settings as Href on Href.SettingId = 25
		left join tb_Settings as Src on Src.SettingId = 24
		left join tb_Utility_Polygons maps on poles.MapFk = maps.Utility_PolygonId 
		left join tb_Utility_Polygons sections on poles.SectionFk = sections.Utility_PolygonId
		left join tb_XMissionStructures_PoleSpecies species on poles.SpeciesFk = species.XMissionStructures_PoleSpeciesId
		left join tb_XMissionStructures_PoleClasses classes on poles.ClassFk = classes.XMissionStructures_PoleClassId
		left join tb_Distribution_Lengths lenghts on poles.Length = lenghts.LengthId
	where
		Poles.Utility_Asset_DistributionId = @PoleId
	for xml path ('Pole')

	)
	return @html

END

